﻿
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public DataRow Convert2ICC_QUERYTB(DataRow dwInput, string ITXDBVERSION, bool IsConvert = false)
        {
            DataSet SResult = new DataSet();
            SResult = QueryICC_QUERYTB(dwInput, 0);
            DataRow dwReturn = null;
            if (SResult.Tables[0].Rows.Count > 0)
            {
                dwReturn = SResult.Tables[0].Rows[0];
            }
            else
            {
                dwReturn = SResult.Tables[0].NewRow();
            }
            switch (ITXDBVERSION)
            {
                case "YGR":
                case "JLT":
                default:
                    dwReturn["VKPMANDANT"] = "00";
                    dwReturn["VKPAUFKRS"] = "  ";
                    dwReturn["VKPAUFLFD"] = "000000";
                    dwReturn["VKPAUFPOS"] = "001";
                    dwReturn["C1"] = " ";
                    dwReturn["C2"] = "  ";
                    dwReturn["ABPMANDANT"] = "00";
                    dwReturn["ABPAUFKRS"] = "  ";
                    dwReturn["ABPAUFLFD"] = "000000";
                    dwReturn["ABPAUFPOS"] = "001"; 
                    break;
            }
            if (IsConvert)
            {
                foreach (DataColumn cm in dwInput.Table.Columns)
                {
                    if (dwInput[cm.ColumnName].ToString() == "")
                        continue;
                    if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                    {
                        dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                    }
                }
            }
            return dwReturn;
        }
        public void MapICC_QUERYTB(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveICC_QUERYTB(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet Result = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            Result = QueryICC_QUERYTB(dwData);
            if (Result.Tables.Count > 0)
            {
                tbLocal = Result.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }

            MapICC_QUERYTB(ref dwLocal, dwData);

            if (!flgExist)
            {
                dwLocal["ROWNID"] = GetSeq();
                InsertICC_QUERYTB(dwLocal, 1);
            }
            UpdateICC_QUERYTB(dwLocal);
        }
        public void InsertICC_QUERYTB(DataRow dwData, int option = 1)
        {
            string table = "VVPACK";  //VVPMANDANT	VVPLIEFSCH	VVPPACKNR
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "INSERT INTO ICC_QUERYTB(C2,EXART,VKPMANDANT, VKPAUFKRS,VKPAUFLFD, VKPAUFPOS, ABPMANDANT,ABPAUFKRS,ABPAUFLFD,ABPAUFPOS)"
                                + " VALUES (:C2,:EXART,:VKPMANDANT,: VKPAUFKRS,:VKPAUFLFD,: VKPAUFPOS,: ABPMANDANT,:ABPAUFKRS,:ABPAUFLFD,:ABPAUFPOS )";
                    or1.Parameters.Add("C2", OracleType.NVarChar).Value = dwData["C2"].ToString();
                    or1.Parameters.Add("EXART", OracleType.NVarChar).Value = dwData["EXART"].ToString();
                    or1.Parameters.Add("VKPMANDANT", OracleType.NVarChar).Value = dwData["VKPMANDANT"].ToString();
                    or1.Parameters.Add("VKPAUFKRS", OracleType.NVarChar).Value = dwData["VKPAUFKRS"].ToString();
                    or1.Parameters.Add("VKPAUFLFD", OracleType.NVarChar).Value = dwData["VKPAUFLFD"].ToString();
                    or1.Parameters.Add("VKPAUFPOS", OracleType.NVarChar).Value = dwData["VKPAUFPOS"].ToString();
                    or1.Parameters.Add("ABPMANDANT", OracleType.NVarChar).Value = dwData["ABPMANDANT"].ToString();
                    or1.Parameters.Add("ABPAUFKRS", OracleType.NVarChar).Value = dwData["ABPAUFKRS"].ToString();
                    or1.Parameters.Add("ABPAUFLFD", OracleType.NVarChar).Value = dwData["ABPAUFLFD"].ToString();
                    or1.Parameters.Add("ABPAUFPOS", OracleType.NVarChar).Value = dwData["ABPAUFPOS"].ToString();
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
        public DataSet DeleteICC_QUERYTB(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM ICC_QUERYTB";  
                    break;
                case 2:
                    or1.CommandText = "DELETE FROM ICC_QUERYTB WHERE RTRIM(EXART)=:EXART";
                    or1.Parameters.Add("EXART", OracleType.NVarChar).Value = dwData["EXART"].ToString().Trim();
                    break; 
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "ICC_QUERYTB";
            Return = Result;
            return Return;
        }
        public DataSet QueryICC_QUERYTB(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM ICC_QUERYTB WHERE ROWNUM<2";
                    break;
                case 1:
                    or1.CommandText = "SELECT * FROM ICC_QUERYTB WHERE   C2=:C2"; ;
                    or1.Parameters.Add("C2", OracleType.NVarChar).Value = dwData["C2"].ToString();
                    break;
                case 9999:
                    or1.CommandText = "SELECT * FROM ICC_QUERYTB";
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "ICC_QUERYTB";
            Return = Result;
            return Return;
        }
        public void UpdateICC_QUERYTB(DataRow dwData, int options = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (options)
            {
                case 1:
                    #region====Command====
                    or1.CommandText = "UPDATE ICC_QUERYTB SET  "
                        + "PLANID=:PLANID,"
                        + "RGROUP=:RGROUP,"
                        + "RULEID=:RULEID,"
                        + "ARTSTAGE=:ARTSTAGE,"
                        + "ORDERNUM=:ORDERNUM,"
                        + "ART=:ART,"
                        + "COLOR=:COLOR,"
                        + "FINISH=:FINISH,"
                        + "AUFTYP=:AUFTYP,"
                        + "QTYEND=:QTYEND,"
                        + "QTYENDDES=:QTYENDDES,"
                        + "R01=:R01,"
                        + "R01DES=:R01DES,"
                        + "R02=:R02,"
                        + "R02DES=:R02DES,"
                        + "F01=:F01,"
                        + "F01DES=:F01DES,"
                        + "F02=:F02,"
                        + "F02DES=:F02DES,"
                        + "JNUM=:JNUM,"
                        + "JNUMDES=:JNUMDES,"
                        + "JWASTGE=:JWASTGE,"
                        + "JWASTAGEDES=:JWASTAGEDES,"
                        + "QTYMAT=:QTYMAT,"
                        + "REMARK=:REMARK "
                        + " WHERE  "
                        + "   ROWNID=:ROWNID ";
                    #endregion
                    #region====Parameters====
                    or1.Parameters.Add("ROWNID", OracleType.NVarChar).Value = dwData["ROWNID"].ToString();
                    or1.Parameters.Add("PLANID", OracleType.NVarChar).Value = dwData["PLANID"].ToString();
                    or1.Parameters.Add("RGROUP", OracleType.NVarChar).Value = dwData["RGROUP"].ToString();
                    or1.Parameters.Add("RULEID", OracleType.NVarChar).Value = dwData["RULEID"].ToString();
                    or1.Parameters.Add("ARTSTAGE", OracleType.NVarChar).Value = dwData["ARTSTAGE"].ToString();
                    or1.Parameters.Add("ORDERNUM", OracleType.NVarChar).Value = dwData["ORDERNUM"].ToString();
                    or1.Parameters.Add("ART", OracleType.NVarChar).Value = dwData["ART"].ToString();
                    or1.Parameters.Add("COLOR", OracleType.NVarChar).Value = dwData["COLOR"].ToString();
                    or1.Parameters.Add("FINISH", OracleType.NVarChar).Value = dwData["FINISH"].ToString();
                    or1.Parameters.Add("AUFTYP", OracleType.NVarChar).Value = dwData["AUFTYP"].ToString();
                    or1.Parameters.Add("QTYEND", OracleType.NVarChar).Value = dwData["QTYEND"].ToString();
                    or1.Parameters.Add("QTYENDDES", OracleType.NVarChar).Value = dwData["QTYENDDES"].ToString();
                    or1.Parameters.Add("R01", OracleType.NVarChar).Value = dwData["R01"].ToString();
                    or1.Parameters.Add("R01DES", OracleType.NVarChar).Value = dwData["R01DES"].ToString();
                    or1.Parameters.Add("R02", OracleType.NVarChar).Value = dwData["R02"].ToString();
                    or1.Parameters.Add("R02DES", OracleType.NVarChar).Value = dwData["R02DES"].ToString();
                    or1.Parameters.Add("F01", OracleType.NVarChar).Value = dwData["F01"].ToString();
                    or1.Parameters.Add("F01DES", OracleType.NVarChar).Value = dwData["F01DES"].ToString();
                    or1.Parameters.Add("F02", OracleType.NVarChar).Value = dwData["F02"].ToString();
                    or1.Parameters.Add("F02DES", OracleType.NVarChar).Value = dwData["F02DES"].ToString();
                    or1.Parameters.Add("JNUM", OracleType.NVarChar).Value = dwData["JNUM"].ToString();
                    or1.Parameters.Add("JNUMDES", OracleType.NVarChar).Value = dwData["JNUMDES"].ToString();
                    or1.Parameters.Add("JWASTGE", OracleType.NVarChar).Value = dwData["JWASTGE"].ToString();
                    or1.Parameters.Add("JWASTAGEDES", OracleType.NVarChar).Value = dwData["JWASTAGEDES"].ToString();
                    or1.Parameters.Add("QTYMAT", OracleType.NVarChar).Value = dwData["QTYMAT"].ToString();
                    or1.Parameters.Add("REMARK", OracleType.NVarChar).Value = dwData["REMARK"].ToString();
                    #endregion
                    break;
            }

            Connect2Proxy(or1);
        }
    }
}
